- About the Series
- Introduction
- Continuing with Calculated Members
- Next in Our Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Continuing with Calculated Members
To this point, 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). In the next section, we will explore the creation of a calculated member of a standard dimension and work through some exercises to see why this is useful.
Creating an Aggregate Member
One of the best uses of a calculated member within a standard dimension is as a grouping device, from which aggregations can be based, as we will see in our next practice set. We create non-measure calculated members by specifying the dimension to which they belong, as well as the point within the hierarchy that they inhabit. Although the process is a bit more involved than the corresponding set of steps for a calculated measure, you will get a good understanding of how to derive and use such a calculated member in a sequenced set of steps.
TIP
Saving the step as a separate query, with each progressive stage of the build process, provides a way to return to prior steps for review; or, in the case of a misstep, provides an easy return path from which to recover. This is the process I use in many development efforts in which I am encountering new concepts and I want to reinforce my foundation at every opportunity.
Let's get started by returning to the Sample Application and by setting up a core query from which we will build the next key elements of MDX. We will begin where we left off, with the query we previously saved as Step6.
Modify the comment line in the query to read -–Step2-1.
Save the query as Step2-1 before making further alterations (just as a precaution).
Within the SELECT statement of the query, modify the section in which we define the rows of our result set (just after the ON COLUMNS section) to the following:
Step6 Affected Section:
{ [Measures].[Store Sales], 9 [Measures].[Store Cost], 9 [Measures].[Store Profit]} ON ROWSNew Section:
CrossJoin({ [Measures].[Store Sales], 9 [Measures].[Store Cost], 9 [Measures].[Store Profit]}, 9 {[Product].[All Products].Children}) ON ROWS
The modified query should appear in its entirety in the Query pane as shown in Figure 1.
)
Figure 1 Step 2-1, with modifications annotated, as it appears in the Query pane.
The effect of the change above is to nest all children of the Product dimension in our row axis. Our entry also provides a review of the CrossJoin function as part of fitting our result to be able to better identify the characteristics of aggregation in later steps. Our intent here is to establish a context from which we can see the practical applications of aggregation in MDX.
Execute the query by clicking the Run button.
The result set appears in Figure 2.
Figure 2 Step2-1 results.The results set shows the desired nesting, which is intended to give us a level of members to group for purposes of practicing the use of non-measure calculated members. As before, we will leave formatting as a later consideration and focus on the retrieval of data.
Save the query with changes as Step2-1.
)
Figure 2To set the backdrop of a business requirement, let's begin by assuming that information consumers have communicated a need to basically group the three types of products that we see into food and non-food groups. Since we have a non-food group already (Non-Consumable), our task is simply to group, or aggregate, the Food and Drink categories into a single group that we will call Consumable. We begin by creating a non-measure calculated member to do just that.
Modify the comment line in the query to read -–Step2-2.
Within the WITH statement of the query, immediately following the SOLVE_ORDER = 2, FORMAT_STRING = 'Percent' line, insert the following:
MEMBER [Product].[All Products].[Consumable] AS 9 '[Drink]+[Food]'At the last line (just above the FROM line) within the SELECT statement of the query, make the following modifications:
Step2-1 Affected Line:
{[Product].[All Products].Children}) ON ROWSStep2-2 Modified Line:
{[Product Family].Allmembers}) ON ROWS
With these insertions, we are creating a new calculated member, Consumable, to group the Food and Drink categories. We are establishing the parent of the new calculated member as All Products, which coincides with the hierarchical level of the members of Product Family. The .AllMembers function (as opposed to the .Members function) is required to display calculated members.
NOTE
A study of the metadata, something that can be done conveniently from the Sample Application's Metadata tree, or from the Analysis Server console for the respective cube, is critical for an understanding of the interrelationships of the levels and members of the hierarchy. Some views display members to appear multiple times in the tree. While this is initially confusing for many who are new to the concepts involved, practice will bring a comfort level with the tool, as well as a capability to understand the levels themselves. We need to grasp the idea of parallel levels, so we can create and display calculated members to occupy equivalent levels and to obtain the results we seek.
The modified query should appear in the Query pane, as shown in Figure 3.
)
Figure 3 Step2-2 with modifications annotated, as it appears in the Query pane.
Execute the Step2-2 query by clicking the Run button.
The result set appears in Figure 4.
Figure 4 Step2-2 results.We see the newly created calculated member Consumable appear, and a quick sample check of the totals of the respective Food and Drink categories confirm that the SUM function is working in conjunction with the Consumable member to produce an accurate total.
Save the query with changes as Step2-2.
)
Figure 4We have created an aggregate member for a small group of members (actually only two categories: Food and Drink), in which using the + sign to perform the aggregation is convenient. Let's look at an example within the same query, in which we can examine the best way to perform an aggregation when multiple values are involved, and the + sign route would prove to be somewhat labor-intensive. First, we need to establish a scenario in which we have multiple values to add. We do this by again seeking a lower level in the hierarchical structure via our core query.
Modify the comment line in the query to read -–Step2-3.
Remove the aggregate calculated member that we created in Step2-2 by removing the following from the WITH clause of the query:
MEMBER [Product].[All Products].[Consumable] AS 9 '[Drink]+[Food]'Within the SELECT statement of the query, at the last line (just above the FROM line), make the following modifications:
Step2-2 Affected Line:
{[Product Family].Allmembers}) ON ROWSStep2-3 Modified Line:
{[Product Department].Allmembers}) ON ROWSWith this modification, we establish a lower level of nesting, in order to have more items with which to work for aggregation purposes.
Query 2-3 appears in the Query pane as shown:
Figure 5 Step2-3 with modifications noted, as it appears in the Query
pane.Execute the Step2-3 query by clicking the Run button.
A partial result set appears Figure 6.
Figure 6 Step2-3 partial results.
We note the appearance of the Product Department members in the row axis—an excellent place from which to explore the aggregation of multiple members.
Save the query with changes as Step2-3.
)
Figure 5)
Figure 6For now, let's say that we have been tasked with combining some of the multiple department classifications into a group (similar to that we created above) to contain only Consumable items (as opposed to non-food, non-drink merchandise). Although higher hierarchical levels make the distinction clear, we want to obtain a total, for this example, of the consumable items at the Product Department level that is displayed.
We begin again by creating a non-measure calculated member to do just that.
Modify the comment line in the query to read -–Step2-4.
Within the WITH statement of the query, immediately following the SOLVE_ORDER = 2, FORMAT_STRING = 'Percent' line, insert the following:
MEMBER[All Products].[Drink].[Consumable] AS 9 'SUM([Product Family].Members)'
Again, while it may appear confusing to anyone not familiar with the hierarchical structure of the Warehouse and Sales cube, we are establishing the new calculated member, again called Consumable, at a level that is, in effect, parallel to those at the level upon which we want to report. At this juncture, Consumable is aggregating all departments. We will adjust this in the next step to exclude non-consumable items. Note, too, that the aggregation is set to occur at another dimensional level (Product Family). I chose this level because there is less ambiguity in consumable versus non-consumable product classifications, but there are certainly other ways to accomplish the same task.
The modifications to the CrossJoin function within the SELECT clause, which we made in the last step when we removed the first Consumable calculated member, remain adequate for our display purposes, so no further alterations will be required at this point.
The modified query should appear in the Query pane, as shown in Figure 7.
)
Figure 7 Step2-4, with calculated member creation circled, from the Query
pane.
Execute the Step2-4 query by clicking the Run button.
The result set partially appears in Figure 8.
Figure 8 Step2-4 partial results.
)
Figure 8We see the aggregate calculated member Consumable appear, and a quick sample check of the totals of the respective Product Department row items confirms that the SUM function is working in conjunction with the modified Consumable member to produce a total.
Now, let's make a further enhancement to exclude the non-consumable items and cause Consumable to reflect accurately the combined values of the food-related items, in accordance with the requirement we are attempting to meet.
Save the query with changes as Step2-4.
Modify the comment line in the query to read -–Step 2-5.
Within the WITH statement of the query, immediately following the MEMBER[All Products].[Drink].[Consumable] AS line, modify the existing definition of the calculated member Consumable, as shown below:
Step2-4 Affected Line:
'SUM([Product Family].Members)'
Step2-5 Modified Line:
'SUM(EXCEPT([Product Family].Members,{[Non-Consumable]}))'
The modification above acts to make the SUM function include all except the members that are classed as Non-Consumable at the Product Family level. Again, we are referencing Product Family here (another hierarchical level entirely) because it conveniently classifies the non-consumables, unlike the level upon which we are required to report. As mentioned earlier, a grasp of the hierarchical structure is key to the employment of such efficiencies; we can thus avoid a cumbersome exclusion mechanism and leverage attributes of the data that although perhaps not foreseen as useful at our specific level during cube design, can still be useful in getting us where we need to be.
Query 2-5 appears in the Query pane, as shown in Figure 9.
)
Figure 9 Step2-5, with modification noted, as it appears in the Query
pane.
The SELECT portion of the query remains adequate for our immediate retrieval needs.
Execute the Step2-5 query by clicking the Run button.
The result set appears partially displayed in Figure 10.
Figure 10 Step2-5 partial results.
)
Figure 10A quick test of the math again verifies that our modifications to the Consumable calculated member have done the job. We are effectively excluding the non-consumable items at the Product Department level, based upon their membership in the Non-Consumable group at the Product Family level, and thus delivering a total amount for consumables at the Product Department level. Once again, we can meet the business requirements with our MDX query.
Save the query with changes as Step2-5.
The results of our latest sequence of steps have shown the capabilities that we have available when we need to aggregate a group of dimension members for a single query. Many iterations of the approach can be applied and various nuances conceived to gain the advantage offered by aggregation members. Options might include use of a calculated member, such as the one we have derived as a filter, to "narrow" the selection of data in an existing report to focus on a key group of members—be it geographical, organizational, or any number of other possibilities that might be supported by a good hierarchical design in the cube.
WARNING
Be careful of injudicious use of aggregation in the WHERE clause. Performance can be adversely affected if we make too heavy use of this approach. If alternate aggregation is a recurring requirement, creation of a second hierarchy for the affected dimension might mean better performance.)
We could substitute the AGGREGATE function name for SUM in our query above and obtain the same results. The AGGREGATE function allows us to give more detailed instructions for defining the type of aggregation we require (such as SUM, MIN, MAX, AVG, and so forth), which calls for more than the characteristics of a simple summary function. For more information, see the Books Online reference that is installed as a part of a Typical MSSQL Server 2000/Analysis Services installation, or that is available from the product CDs, the Microsoft site, or other sources.
A Quick Overview of Cumulative Totals
A natural extension of our exploration into aggregations lies in the area of cumulative totals. We can extend the SUM function that we have used to create calculated measures on standard dimensions above, and accomplish the same effect with a new calculated measure. A common use of the SUM function to do so would be to create a cumulative total.
The calculation of a cumulative, or "running," total is a common requirement in the business environment. We examine ways to deliver this analytical component to our information consumers in the following steps. In addition, we will discuss the functions that MDX provides for needs just such as these. We begin with a new query this time, so be sure to save the work we have accumulated (no pun intended) to this point for later reference, if appropriate.
Create a new query.
Insert the following via the Query pane:
--Step3-1 SELECT 9 [Month].Members ON COLUMNS, 9 {[Measures].AllMembers} ON ROWS 9 FROM [Warehouse and Sales]Execute the query by clicking the Run button.
The result set appears partially displayed in Figure 11.
Figure 11 Step3-1 partial results.
)
Figure 11After the manner of our earlier examples, we have initially established a core query upon which we will perform a short series of steps to gain an understanding of larger concepts. Notice that all months appear (the numbers identifying them are the actual Member Names in the Warehouse and Sales cube). Our use of .AllMembers in the second line of the SELECT statement allows us to see all members, including calculated members. The results are a bit overwhelming at present.
Save the query with changes as Step3-1.
Let's add a calculated member to get closer to our objective of producing a running total.
Modify the comment line in the query to read -–Step3-2.
Add the following before the SELECT statement:
--Step3-2 WITH MEMBER [Measures].[Cumulative] AS 9 'SUM([Time].[1997].[Q1].[1]: 9 [Time].CurrentMember, [Store Sales Net])' 9
The addition above contains a SUM function whose syntax contains two arguments that we are exploring for the first time. The layout of the function appears as follows:
Sum(«Set»[, «Numeric Expression»])
We used the first argument, which is required, in our groupings in the last example. The second argument, << Numeric Expression>>, is optional and virtually never used when creating a non-measure calculated member, as was the case in our earlier use of the function. However, any time we create a calculated measure, as we are doing with Cumulative above, we would expect to use the second argument; the <<Numeric Expression>> argument instructs the function about which measure to use as the basis in creating the new measure.
We placed the basis measure, Store Sales Net, in the <<Numeric Expression>> position and added a set that defined the time period range we wanted to include, separating the beginning time period ([Time].[1997].[Q1].[1]) and ending time period ([Time].CurrentMember) of the range we wanted with the colon (:) we see above. The .CurrentMember placement is the key behind the roll forward effect we obtain in each of the columns we will see next.
Our query appears in the Query pane, as shown in Figure 12.
)
Figure 12 --Step3-2, with addition noted, as it appears in the Query
pane.
Execute the query by clicking the Run button.
The result set appears partially displayed in Figure 13.
)
Figure 13 Step3-2 partial results.
We can see that the Cumulative values are, indeed, accumulating the Store Sales Net values—each month adding the current month's value to the total and delivering the "running total." This accumulation continues until the first month of 1998, after which we see the Cumulative total remaining the same for the months that follow because the cube contains no values for Store Sales Net after Month 12 of 1997.
Save the query with changes as Step3-2.
MDX provides functions to calculate cumulative values. One of the most commonly used is the PeriodsToDate function, an instance of which we will build into our query as the next step to illustrate its use. Moreover, we will restrict the data we return to make the result set more compact and hopefully easier to use by information consumers.
Create a new query.
Type the following into the Query pane:
--Step3-3 WITH 9 MEMBER [Measures].[YTD Shipped] AS 9 'SUM(PeriodsToDate([Time].[Year]), [Units Shipped])' 9 SELECT 9 [Month].Members ON COLUMNS, 9 {[Measures].[Units Shipped],[YTD Shipped]} ON ROWS 9 FROM [Warehouse and Sales] 9
While the above is intended to create a cumulative total like the last example, we give the cumulative calculated member a name that makes sense to the user (YTD Shipped) based upon what it is (a cumulative total whose basis is Units Shipped) and what it does (provides a running total over the months of the year.) In the PeriodsToDate function, we set Year as the parent. The function behaves just as it did with the date range we inserted in the last example, creating a set whose ending point is the current member of what has been defined as the Time dimension.
Our query appears in the Query pane as partially shown in Figure 14.
)
Figure 14 Step3-3 as it partially appears in the Query pane.
Execute the query by clicking the Run button.
The result set appears partially displayed in Figure 15.
Figure 15 Step3-3 partial results; "reset point" selected for YTD shipped.
)
Figure 15My intent in the above partial view is to show the "reset" of the YTD Shipped value at the Month 12, 1997 to Month 1, 1998 transition. Within the PeriodsToDate function, the beginning member of the set becomes the first member with the same common parent as the ending member. The cumulative feature begins again at the emergence of a new parent, as in the case above, with the change of the Year to 1998.
Save the query with changes as Step3-3.
Let's further illustrate the operation of the PeriodsToDate function by making a change in the argument (the portion of the function enclosed by parentheses) whose role in the "reset" point placement within the result set will be illustrated once again.
Modify the comment line in the query to read -–Step 3-4.
Within the WITH statement of the query, modify the existing definition of the calculated measure, as shown below:
Step3-3 Affected Lines:
MEMBER [Measures].[YTD Shipped] AS 9 'SUM(PeriodsToDate([Time].[Year]), [Units Shipped])'
Step3-4 Modified Lines:
MEMBER [Measures].[QTD Units Shipped] AS 9 'SUM(PeriodsToDate(), [Units Shipped])'
The name of the calculated measure is changed to QTD Units Shipped, and the PeriodstoDate function has the argument removed, leaving only empty parentheses.
Within the SELECT statement of the query, modify the following:
Step3-3 Affected Lines:
{[Measures].[Units Shipped],[YTD Shipped]} ON ROWSStep3-4 Modified Lines:
{[Measures].[Units Shipped],[QTD Units Shipped]} ON ROWS
The modification here is simply to adjust the displayed results set for the new calculated member name (QTD Units Shipped), as defined in the WITH statement above.
Query 3-5 appears in the Query pane in its entirety, as shown in Figure 16.
)
Figure 16 Step3-5, with modifications noted, as it appears in the Query
pane.
The SELECT portion of the query remains adequate for our immediate retrieval needs.
Execute the Step3-5 query by clicking the Run button.
The result set appears partially displayed in Figure 17.
Figure 17 Step3-5 partial results, with "reset" examples
circled.
)
Figure 17We see that the function now causes a reset at the quarter breaks. The effect of removing the argument, and therefore not specifying the parent involved in the time dimension, results in the "lowest common parent" being assumed; the quarter therefore assumes the role. Thus, we see the operation of the PeriodsToDate function illustrated once more.
Save the query with changes as Step3-5.
Any time we need to query week-to-date, month-to-date, quarter-to-date, or year-to-date periods specifically, we will find that the richness of the MDX language is again apparent: We can use the following functions as shortcuts to achieve results like those we have explored in this section:
- WTD(<member>)
- MTD(<member>)
- QTD(<member>)
- YTD(<member>)
Selecting and Retrieving Member Properties
The final section of this lesson focuses on the selection of member properties within our MDX queries. MDX queries allow for the selection of member properties, which are defined for the dimensions themselves, or on individual member levels. We can make use of the member properties within calculated members. Member properties can be useful, as we will see with the next example.
Special considerations come into play, however, in using member properties in calculated members, not the least of which is the fact that the values stored at the member property level, even numeric values, are stored as text strings. This means that conversion of the string to a number is required to perform any operations in which the property is supposed to behave like a number. We will illustrate this set of circumstances, as well as a means of handling it, to achieve "rollup" of the properties to higher levels in the simple exercise that follows.
One of the member properties that occur within a dimension of the Warehouse and Sales cube will serve well to illustrate the selection and retrieval of member properties. A great place to get a bird's-eye-view of the metadata of a cube is the Cube Editor in Analysis Services. A key activity for getting a good understanding of the cube structure is obtaining an understanding of the metadata. A quick review of the Store dimension within the tree in the Cube Editor (right-click the cube and select Edit to reach the view from the Analysis Manager console) reveals the presence of member properties for the Store dimension, as shown in Figure 18.
)
Figure 18 A Cube Editor view of the Store Dimension Member
properties.
The member properties include an example of a numerical value, Store SqFt, which contains the square footage area for the respective store members. As we have discovered, although numeric in nature, the member property information is stored as a text string. This provides us with an excellent example for our exercise in selecting and retrieving such properties in a way that will make them useful to Information Consumers.
Create a new query in the Query pane of the Sample Application.
Type the following into the Query pane:
--Step4-1 SELECT 9 {[Measures].[Store Sales]} ON COLUMNS, 9 {[Store].[Store Name].Members} ON ROWS 9 From [Warehouse and Sales]
The above query forms the core of our next exercise.
Our query appears in the Query pane as partially shown in Figure 19.
)
Figure 19 The first step of the new query as it appears in the Query
pane.
Execute the query by clicking the Run button.
The result set appears partially displayed (scrolled to the bottom) in Figure 20.
Figure 20 Step4-1 partial results.
)
Figure 20The purpose of the first-step core query is simply to set up the axes for the enhancements we will make to allow the retrieval and display of member properties. As a useful way to contrast the behavior of the text-string property that we will be attempting to present as a number, we have included a numerical value from the cube, Store Sales, simply to compare to the Store SqFt member property that we will be retrieving.
Save the query with changes as Step4-1.
We now add the syntax required to create a calculated member based upon the Store SqFt member property.
Modify the comment line in the query to read -–Step4-2.
Add the following before the SELECT statement:
WITH MEMBER [Measures].[Store Size] AS 9 'Val(Store.CurrentMember.Properties("Store Sqft"))'
The syntax addition above contains Val, a VBA function that returns the numbers contained in a string as a numeric value of the appropriate type.
Modify the first line of the SELECT statement as follows:
Step4-1 Affected Lines:
{[Measures].[Store Sales]} ON COLUMNS,Step4-2 Modified Lines:
{[Measures].[Store Sales], [Measures].[Store Size]} ON COLUMNS,
Our query now appears in the Query pane, as shown in Figure 21.
)
Figure 21 Step4-2, with additions noted, as it appears in the Query
pane.
Execute the query by clicking the Run button.
The result set appears partially displayed in Figure 22.
)
Figure 22 Step4-2 partial results.
In the results set, we note that the new calculated member, Store Size, appears with zeros filling the fields for which no square footage is defined in the cube (the "nulls" are converted to zeros). We can see that the text strings that occupy the Store SqFt member property have been converted to numerical values because the nulls that exist in the cube for certain members have become zeros instead.
Save the query with changes as Step4-2.
Next, let's change the SELECT clause to retrieve and display not only the Store Names (the lowest level of the Store dimension), but to retrieve and display the "rollup" levels in the hierarchy of Store City, Store State, and Store Country.
Modify the comment line in the query to read -–Step 4-3.
Within the SELECT clause of the query, immediately following the {[Measures].[Store Profit], [Measures].[Store Size]} ON COLUMNS, line, modify the next existing line as follows:
Step4-2 Affected Line:
{[Store].[Store Name].Members} ON ROWSStep4-3 Modified Line:
{[Store].Members} ON ROWS
The modification above changes the SELECT clause to retrieve the members of the Store dimension in general, which will include all members—not just Store Names. This will mean that the individual stores, as well as the level members to which they belong, will be retrieved.
The modified query appears in the Query pane in its entirety, as shown in Figure 23.
)
Figure 23 Step4-3, with modification circled, as it appears in the Query
pane.
Execute the Step4-3 query by clicking the Run button.
The result set appears partially displayed in Figure 24.
)
Figure 24 Step4-3 partial results.
At the rollup levels, we have #ERR appearing as the result in the affected fields. This occurs because although we have converted the Store SqFt member property to a numeric value, we have yet to make provision for an aggregation to occur; the square footage values are held at the member level, not at the various dimension levels to which the stores belong in our cube. We handle this with the next step, in which we create a calculated member, based upon the Store dimension, to sum the individual store square footages at the appropriate rollup levels.
We create a calculated member that correctly handles the summing while leaving our original calculated member in place for reference purposes.
Save the query with changes as Step4-3.
Modify the comment line in the query to read -–Step 4-4.
Within the WITH statement of the query, immediately following the 'Val(Store.CurrentMember.Properties("Store SqFt"))', line, add the following syntax to create a new calculated member for Total Store Area:
MEMBER[Measures].[Total Store Area] AS 9 'SUM(Descendants([Store].CurrentMember, [Store Name]) , [Store Size])'
In the first line within the SELECT statement of the query, modify the syntax to include selection of the new calculated member, Total Store Area:
Step4-3 Affected Line:
{[Measures].[Store Sales], [Measures].[Store Size]} ON COLUMNS,Step4-4 Modified Line:
{[Measures].[Store Sales], [Measures].[Store Size], 9 [Measures].[Total Store Area]} ON COLUMNS,
The modified query appears in the Query pane, as shown in Figure 25.
)
Figure 25 Step4-4, with modification circled, as it appears in the Query
pane.
Execute the Step4-4 query by clicking the Run button.
The result set appears partially displayed in Figure 26.
)
Figure 26 Step4-4 partial results.
The "parallel calculated member," Total Store Area, appears alongside its predecessor—and appears to have overcome the limitations of the Store Size calculated member, as well. We see rollups occurring accurately, in that the individual stores' square footages now add correctly in the corresponding Store City, Store State, and Store Country levels.
Save the query with changes as Step4-4.
Were this a "real-world" report for a client, I would perform formatting at this stage after removing the Store Size calculated member from the SELECT statement, so as not to display it. We will leave the query at this stage, however, with an invitation to all readers to play with the layout further as time is available.
With this example, we have advanced another step with our calculated member, retrieving a member property and returning it after a conversion with the val function, in a way that allows it to deliver more value to the information consumers. We have taken steps to leverage the property beyond its standard capability to store information for the member that contains it, and to provide rollup values through the SUM function. This demonstrates additional possible uses for the aggregation of values using a non-measure calculated member.