- About the Series
- Introduction
- Creating Calculated Members
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Creating Calculated Members
We left the previous article with a preview of building calculated members, in which we undertook the creation of a simple dynamic calculated member. Our knowledge of sets at this stage prepared us to begin a discussion of calculated members in general, and we created a basic dynamic calculated member to conclude our introduction to MDX queries, while demonstrating yet another capability of this powerful OLAP reporting language.
A Return to Dynamic Calculated Members
As discussed earlier, 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 can 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 the focus of the first sections of our lesson. The WITH operator in an MDX query creates a dynamic calculated member; that is, the member, and therefore the calculation that the member performs, exists only 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.
Our first exercise will be to create a basic dynamic calculated member in an MDX query. We will then build upon our knowledge to add more capabilities while discussing potential uses for our calculated members. We will begin by firing up the MDX sample application once again because it provides an excellent platform from which to learn about MDX:
Go to the Start button on the PC; navigate to Microsoft SQL Server, Analysis Services; then navigate to the MDX Sample Application.
The Connect dialog box appears (see Figure 1).
Figure 1 The Connect dialog box for the MDX sample application.Figure 1 depicts the name of my server, MOTHER, and properly indicates that we will be connecting via the MSOLAP provider (the default).
Click OK.
NOTE
We might also choose to cancel the dialog box and connect later by clicking Connect on the File menu.
The MDX Sample Application window appears.
Clear the top area (the Query pane) of any remnants of queries that might appear.
Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.
Select the HR cube in the Cube drop-down list box.
)
Figure 1The MDX Sample Application window should resemble that shown in Figure 2, complete with the information from the HR cube displaying in the Metadata tree (the left section of the Metadata pane).
)
Figure 2 MDX Sample Application window.
Many similarities exist between the arrangements of objects in the Metadata pane and the Calculated Member Builder. We will discuss various attributes of the MDX sample application when they are relevant to the exercises we undertake, but it is highly useful to explore the Books Online for a wealth of detail about the application.
To begin our examination of calculated members, we will create a simple Average Salary member:
Type the following query into the Query pane:
SELECT [Measures].Members ON COLUMNS, [Employees].Members ON ROWS FROM [HR]
Click the Run Query button (the button sporting the green arrowhead-shaped icon.
A tooltip will alight when the cursor is placed upon the button to positively identify it.
The rather lengthy results set are partially shown in Figure 3.
)
Figure 3 Initial results set from the HR cube.
Our query has returned the three measures it found in the HR cube as column headings:
- Org Salary
- Count
- Number of Employees
The WITH operator can be used in MDX queries to define our calculated members, among other uses. WITH precedes the SELECT clause in the statement, stipulating a section in which the calculated member (together with named sets) is defined in the query. Let's take a look at placement and use of the WITH operator, which will allow us to return data and calculations in a straightforward manner.
In our example, we will create a calculated member, which we will call Average Salary, to show a simple salary average for the employees/groups of employees that are returned in our result set. Note that the Average Salary calculation is defined as a member as a result of using the WITH statement (hence the name "calculated member").
Insert the following into the Query pane, into the existing query before the SELECT clause:
WITH MEMBER [Measures].[Average Salary] AS '[Org Salary]/[Number of Employees]'
The query should now resemble that shown in the Query pane shown in Figure 4.
Figure 4 The initial query; WITH clause added to create a
calculated member.Click the Run Query button.
The results set appears, but it is identical to the last (refer to Figure 3).
)
Figure 4Our query returns the new result set—and nothing has changed since before the addition of the WITH statement. The .Members portion of the column axis we have defined does not include calculated members. To cause calculated members to appear, we need to insert the AllMembers function.
Adjust the query, substituting the AllMembers function, as shown in Figure 5.
Figure 5 The modified query—adding the AllMembers function.Click the Run Query button.
The results set appears—this time revealing the new calculated member Average Salary, as shown in Figure 6.
Figure 6 The calculated member appears—twice!
)
Figure 5)
Figure 6But what is happening here? We have two calculated members that appear to calculate the same Average Salary figure. The first calculated member, Avg Salary, is a calculated member that was defined at the cube level, built into the sample as an illustration of the mechanics behind doing so. We find this out because the AllMembers function results in the display of all calculated members as well as standard members; and in our case, we happen to have created a calculation that is identical (except for formatting) to the non-query defined calculated member that already existed in the cube structure.
This serves as a good illustration of the advisability of familiarizing ourselves with the structure of a cube before adding calculated members. In this case, it also gives us a quick check to see that our calculation is performing as expected, delivering the correct average salary amount (sans formatting, once again). We will leave things as they are because the calculated member that already existed is permanent, but our new Average Salary member, dynamically defined by the query, exists only with the execution of the query.
Add a comment to the query, as shown in Figure 7, for easy identification at a later time (after we run the query, the comment tag will appear in the Queries selector list at the top-right side of the toolbar in the sample application, making it easier to find).
Figure 7 Add a comment for easy identification in the Queries selector
list.Save the query file, naming it CM_AvgSal for easy identification, as a calculated member sample.
)
Figure 7We need only use the Member clause, preceded by the WITH keyword, for each calculated member we wish to add. We will practice this again, with variations, as we advance through this lesson and in later lessons.
Let's get started with an exercise that will gradually build into a multifaceted query that illustrates multiple perspectives of working with calculated members. Let's say, for purposes of illustration, that a group of information consumers has asked us to assist with building a query to show the percent change in the profits of the current quarter (we'll imagine we are in the second quarter of 1997) over the quarter before. The query will rely upon several concepts that we will discuss as we progress.
First, we'll return to the sample application and begin afresh.
Click the New Query button to start a new query.
Select the Warehouse and Sales cube, as shown in Figure 8.
Figure 8 Select the Warehouse and Sales cube.Type the following basic query, complete with the comment line, into the Query pane:
--Step1 SELECT {[Time].[1997].[Q1], [Time].[1997].[Q2]} ON COLUMNS, { [Measures].[Store Sales], [Measures].[Store Cost]} ON ROWS FROM [Warehouse and Sales]
)
Figure 8Our intent here is to set up the basis for further exploration. The --Step 1 comment indicates the step of our build, and we'll repeat this process at each step, saving each before continuing so as to have a "fallback" position, in case we get off-track and need to return to the "last known good configuration." This has saved me many hours in lost re-creation time when just such a thing has occurred, particularly if I am building logic on-the-fly, and experimenting with each step to achieve a specific result.
The query we typed above makes use of sets, indicated by braces ( { } ), to build the column and row axes.
Execute the query by clicking the Run Query button.
The result set appears in Figure 9.
Figure 9 Step 1 results.
)
Figure 9The basics of calculating profit have been retrieved: Even a non-accountant knows that Store Sales and Store Cost will allow us to derive Store Profit, which has not yet been defined, as a calculated member in the Warehouse and Sales cube. Once we have the Store Profit, our next step is to compute the percent change in profit between Quarter 2 (Q2) and Quarter 1 (Q1) of 1997.
Save the query as Step1, leaving it open for the next step.
Type the following statement into the Query pane before the SELECT clause, replacing the top comment line with --Step 2.
WITH MEMBER [Measures].[Store Profit] AS '[Measures].[Store Sales] - [Measures].[Store Cost]'
After the [Measures].[Store Cost] portion of the SELECT clause (and within the braces), add a comma and then the following line below it, just before the close brace (}) and ON ROWS:
[Measures].[Store Profit]
The complete modified query should appear in the Query pane, as shown in Figure 10.
)
Figure 10 Step 2, as it appears in the Query pane (additions
circled).
The addition we have made in the form of the WITH clause creates a calculated member called Store Profit, and the addition of the newly defined calculated member in the set below instructs the application to return it as an additional row in our result set, bringing us one step closer to meeting the requirements of the information consumers that we described above.
Execute the query by clicking the Run Query button.
The result set appears in Figure 11.
Figure 11 Step 2 results.
)
Figure 11We will handle the formatting later. The important thing at present is that we appear to have achieved the desired result, and have derived and displayed the needed Store Profit amounts with our new calculated member. Let's now add calculation of the next part of the requirement, Percentage Change, to our query.
Save the query with changes as Step2.
Modify the comment line to read --Step3.
Insert the following statement into the Query pane, within the WITH clause, between the [Measures].[Store Cost] portion (do not place a comma after [Measures].[Store Cost]), added in query Step2 and the SELECT clause, replacing the top comment line with --Step 3.
MEMBER [Time].[% Change] AS '([Time].[1997].[Q2]-[Time].[1997].[Q1])/[Time].[1997].[Q1]'After the [Time].[1997].[Q2] portion of the SELECT clause and within the braces, add a comma; then add the following line below it, just before the close brace (}) and ON COLUMNS:
[Time].[% Change]
The complete modified query should appear in the Query pane, as shown in Figure 12.
)
Figure 12 Step 3 as it appears in the Query pane.
The addition we have made in the form of the WITH clause creates a calculated member called % Change, and the addition of the newly defined calculated member in the set below instructs the application to return it as an additional column in our result set.
Execute the query by clicking the Run Query button.
The result set appears in Figure 13.
Figure 13 Step 3 results.
)
Figure 13As we see above, formatting aside, we approach even more closely the objectives of the information consumers. We now have a column that presents the % Change calculation based upon the first calculated member, which derived the delta value between the quarters.
Save the query with changes as Step3, again leaving it open for the next steps.
I make it a practice to save formatting for last when creating queries, writing reports, or anything related to the derivation and subsequent presentation of information because much rework can be saved by avoiding the inevitable changes associated with adding to or modifying a report. Although I may find it hard to resist the urge to "go ahead and clear up the picture," years of experience have made it clear that this is a key kind of discipline to have, especially with elaborate queries and complex reporting.
In direct contradiction to this principle, however, I have found that although carrying out the highly efficient process of presenting a draft of the report/result sets to many information consumers, the appearance of the data often outweighs the content itself. Many of us have experienced the manager/staff member who wants, in effect, to remake the new information product in the likeness of an existing report.
While existing reports can be effective guides to creating new reports, the direct replication approach can be taken too far. I cannot count the times I have been asked to produce reports that must "resemble exactly" their mainframe counterparts—even being asked a couple of times if I could create reports to resemble green bar completely! The issue with this is often one of common sense: the mainframe report presented things in the way it did because it had to. Limitations in the environment dictated very rigid report layouts—layouts that are neither required nor even sensible with the analysis and reporting tools we have today. What's more, to design a report to mirror the outmoded workings of the previous system often means hamstringing the processing speeds and other characteristics of our more evolved packages. Scores of examples exist in "typical" financial reports produced by the standard systems of old.
While my initial reaction to the "identical replication" approach is to attempt to reason with the manager in terms he/she can understand, this appeal to logic is sometimes unsuccessful. Suffice it to say that the client ultimately gets what they want, sometimes to the detriment of the information consumers, and with the effect of handicapping the organization's competitiveness. For the occasional organizations that take this route, there certainly will be competitors who are more interested in the accurate and optimal delivery and presentation of data, and who will take advantage of the powerful features that are available in applications such as Analysis Services.
My point here is that there are certainly ways to format and arrange layouts to resemble almost any model that is preferred. The important thing is to realize that if the selection and/or derivation of the data is optimized from the ground up, with "form following substance," and the formats are being added thoughtfully and globally after data selection considerations have been met, performance of the information product will likely be enhanced.
That being said, let's take a look at some formatting techniques. We can begin by making the % Change cells more compact for ease of use in the design.
Insert the following just ahead of the ([Time].[1997].[Q2]-[Time].[1997].[Q1])/[Time].[1997].[Q1]' part of the WITH clause (last line of the clause):
'100 *
Change the comment line to read --Step 4.
The modified query should appear in the Query pane, as shown in Figure 14.
Figure 14 Step 4, as it appears in the Query pane, with addition
circled.Execute the query.
The result set appears in Figure 15.
Figure 15 Step 4 results.
)
Figure 14)
Figure 15We see the % Change column for the first two rows more closely resembles a percent figure to which most of us can relate, with the currency symbol in front of the top field of the column being a matter for later resolution. This was easily accomplished by the simple addition of the multiplier—one of numerous ways (and not necessarily the most optimal in a production setting) to accomplish the same task.
But the number that appears in the last row of this column, formed by the % Change/Store Profit calculated members' intersection, brings to light a consideration that is somewhat weightier than mere formatting concerns.
Save the query with changes as Step4, leaving it open for the next steps.
Specifying Precedence of Calculation
We have created two calculated members at this stage, Store Profit and % Change, to meet the demands of the information consumers we have referenced. We placed the % Change member on the column axis (similar to the x-axis, for the multidimensionally challenged), and made it a column in our result set. We made the Store Profit member a row (the y-axis equivalent) axis member through our query design. Our focus at this stage is the behavior of the corner cell that results: Depending on the order in which the two calculations are processed, the result in the corner cell is different.
We can easily assume that what the consumers want here is a result that represents the change in overall Store Profit between Q1 and Q2. 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. We can thus control the precedence of calculation of the members, as we will see.
To restate our objective within this step, we need to derive and present the change in Store Profit between Q2 and Q1, not to present an amount based upon the difference in the Store Sales and Store Cost delta values. We thus want the Store Profit 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:
Modify the comment line to read --Step 5.
After the '[Measures].[Store Sales] - [Measures].[Store Cost]' portion of the WITH clause, add a comma and then add the following line below it, just above the MEMBER [Time].[% Change] AS line:
SOLVE_ORDER = 1
After the '100 * ([Time].[1997].[Q2]-[Time].[1997].[Q1])/[Time].[1997].[Q1]' portion of the WITH clause, add a comma and then add the following line below it, just above the SELECT keyword:
SOLVE_ORDER = 2
The modified query should appear, in its entirety, in the Query pane, as shown in Figure 16.
Figure 16 Step 5 with modifications noted, as it appears in the Query
pane.Execute the query.
The result set appears in Figure 17.
Figure 17 Step 5 results.The result matches our expectation, and substantially meets the requirements of the information consumers.
Save the query with changes as Step5, leaving it open for the next steps.
)
Figure 16)
Figure 17We have now completed the derivation of the desired information and set up the layout for its presentation through our query design. Provided that no further calculations or other design criteria are imposed, this might be a good time to clean up our formats with a further enhancement. 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.
Modify the comment line to read --Step 6.
Remove the '100 * that we inserted just ahead of the ([Time].[1997].[Q2]-[Time].[1997].[Q1])/[Time].[1997].[Q1]' part of the WITH clause (in Step4).
Insert a comma, followed by the text below, after the SOLVE ORDER = 1 member property instruction for the Store Profit calculated member we created in the WITH clause of the query:
FORMAT_STRING = '#,##0.00;(#,##0.00)'
Insert a 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'
The modified query should appear in its entirety in the Query pane, as shown in Figure 18.
Figure 18 Step 6, with modifications circled, as it appears in the Query
pane.Execute the query.
The result set appears in Figure 19.
Figure 19 Step 6 results.
)
Figure 18)
Figure 19The result matches our expectation, and helps us to substantially meet the requirements of the information consumers. The recovering CPA in me tends to be accustomed to the amount at the top of a column being preceded by the dollar sign, but we could have added dollar signs to each of the amounts—there are many options available.
Save the query with changes as Step6.
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). The MEMBER clause allows us to create multiple calculated members, as we have seen.
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 resume our exploration of calculated members in our next article, "Building Calculated Members, Part 2," with the creation of a calculated member of a standard dimension, and work through some exercises to see why this is useful.