Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures

Monday Aug 16th 2004 by William Pearson
Share:

Bill Pearson walks through the process to create a derived measure to replace an existing calculated measure, as an option for enhancing query processing performance.

About the Series ...

This article is a member of the series Introduction to MSSQL Server 2000 Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, with each installment progressively adding features and techniques designed to meet specific real-world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 / 3a updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, in cases where MS Office components are presented in the article.

Overview

In dealing with MSAS implementations on a daily basis, and especially when being called upon to tune MSAS implementations performed by others, I come across the less-than-optimal use of calculated members quite often. As most of us know, calculated members are dimensions or measures (depending upon the designated parent dimension) that are constructed, using a formula, from other dimensions or measures in our cubes. A typical example of a calculated member that is designed for a measure, to which we will refer in this article as a calculated measure, is a Profit calculated measure that is created by subtracting a cost / expense measure from a sales / revenue measure. Another common calculated measure is a variance measure, which is created by taking a difference between an actual and a budgeted value (or similar kinds of values), among other approaches.

If the calculation / formula that we use in creating the calculated measure consists of a simple match between two measures, we can often use a derived measure instead. In this article, we will discuss the advantages and disadvantages involved, and compare and contrast the methods of adding these sorts of measures to our cubes. In examining the use of derived measures to enhance cube response times, we will:

  • Discuss the drawbacks in using calculated members in cases where a derived measure might be substituted;
  • Discuss benefits and disadvantages that might accrue through the use of derived measures;
  • Describe an illustrative scenario, upon which we will determine that a derived measure can offer a tuning solution for a group of hypothetical information consumers;
  • Implement a simple solution through creation of a derived measure to replace an existing calculated measure;
  • Explain the results we obtain from the steps we take to accomplish the solution.

Create a Derived Measure

Objective and Business Scenario

In this article, we will examine the use of a derived measure, as a substitute for, and in contrast with, an existing calculated measure that represents a performance bottleneck. For purposes of our practice procedure, we will assume that we have been contacted by a client to investigate slowdowns in query response time when accessing an MSAS cube. We have been informed that reporting response times, in general, have disappointed information consumers throughout the organization. They specifically indicate that using some of the calculated members in the current cube structures lead to report performance issues, although they have no idea as to the root of the problem.

Discussion of the issues with various consumers has revealed that the client, as is often the case these days, failed to become involved in the implementation while it was underway. As the "go live" commitments of the responsible department loomed, the attention of its manager was suddenly "redirected" by upper management. The manager allowed a very brief turnover from the implementation team to a designated employee, whose original position had been recently off shored. Having assigned the employee the administration of the MSAS cubes, the manager dismissed the implementation team without further ado.

While the new support selectee had some experience in a competitor OLAP application, she had spent only a little "cram" time with MSAS, barely progressing from a popular "Baby Steps" book that was once popular on the market. She was confident that she could "figure it out," however, and expressed optimistic confidence that managing the MSAS applications would be a "piece of cake." For that matter, she allowed management to assume that she was an MSAS "guru," and promised to take over where the implementation team left off, and meet the information consumers' evolving needs as they arose.

The "Baby Steps" book, while an excellent introduction to MSAS functionality, failed to equip the new MSAS Administrator to deal with the involved troubleshooting, processing and performance tuning considerations of the real world. Understandably, its focus was teaching MSAS quickly, and in a way that would appeal to non-technical people of varied backgrounds. After the manner of most "populist" technical publications, general concepts, versus optimization and best practices, was, unsurprisingly, the objective.

Upon initially examining the cubes in place, we have noticed many things that could be changed, including the use of lengthy description fields versus (surrogate) id's for member key columns within the cubes, among a host of other items that could be adjusted to optimize both processing and querying performance. One specific item that we have identified, and which is the focus of this article, was the use of various calculated measures when a derived measure might have been constructed. While we will take a single example of this for purposes of our practice exercise, we have actually found several instances where the same procedures might be applied to build and substitute derived measures within the MSAS cubes we have examined at the client location.

The calculated measure upon which we will concentrate in our present article was constructed within the Sales cube to generate a store cost value, with a fixed allocation of marketing costs added. The adjusted cost value, constructed using the calculated measure under consideration, applied an added fifteen percent to the store cost for products that were subjected to given marketing promotions. Products that were not marketed under specific promotions were assigned only the original store cost amounts to their adjusted cost values. Again, this calculated measure is only one example of several such constructions that, as we shall see, can be managed in a way that promotes more efficient query processing.

Considerations and Comments

For purposes of this exercise, we will create the calculated measure as constructed by the erstwhile guru, using the Sales sample cube that accompanies the installation of MSAS. We will then construct a derived measure that will generate the same values, but with enhanced query processing time.

Unlike calculated members (including, of course, calculated measures), whose values are created at runtime, based upon the MDX expression(s) they contain, a derived measure, just as any other cube measure, is calculated during the cube processing cycle, and is stored within Analysis Services. In the case of the calculated member, only its definition is stored, and the values it renders are calculated in memory when specified in a query. Because only the definition is stored, cube processing time ("build time") is not materially increased, and, indeed, depending upon the environment, this might be an appropriate tradeoff. In our present scenario, however, query processing is the more important concern, so we lose the primary benefit behind the choice of a calculated measure to provide the needed values.


Derived measures differ from "ordinary" measures because they take advantage of the flexibility that Analysis Services offers us in modifying the source column property for a given measure. Because they are stored in the cube file, as we have mentioned, they typically mean more efficient query processing. Derived measures, by their nature, are calculated prior to the creation of aggregations. (In contrast, calculated measures are calculated after aggregations are created.) In general, derived measures make sense if they will be called upon frequently, as in reporting scenarios such as that of our hypothetical information consumers. Calculated measures might be a better choice for values that we expect to be used infrequently, again because the tradeoff is tipped more in favor of a lower overhead on the cube processing side, where query response time might not be as high a priority.

We are limited to creating derived measures from the columns of the fact table, since MSAS essentially only offers these columns as options in the measure creation process. However, as many of us have found, a view can be created to contain columns that lie outside the physical fact table, making this limitation a bit less restrictive than it might appear at first blush. Derived measures can extend well beyond simple math, as we shall see in our example, and can apply conditional logic and other nuances in derivation of the measure. The syntax obviously has to fit the database - we will note in our practice example that we use MS Access - friendly syntax, because the FoodMart 2000 data source ships as an MS Access .mdb.

If the sample FoodMart MSAS database, or for that matter the FoodMart .mdb that underlies it, was not installed, or was removed prior to your beginning this article, please see the MSAS documentation, including the Books Online, for the straightforward procedure to restore the database from the archive (.cab) file containing the samples. As of this writing, a copy of the archive can be obtained from the installation CD or via download from the appropriate Microsoft site(s).

Hands-On Procedure

Preparation - Retracing the Steps of the New MSAS Administrator

Let's first create a copy of the original calculated measure, so as to provide a basis of comparison for the derived measure, which we will next create to take its place. To repeat the original requirement, we need to generate an adjusted store cost value, which contains an additional, fixed allocation of marketing costs. Specifically, we will add fifteen percent to the store cost value in any case where a specific marketing promotion was involved.

We will be able to "flag" the use of promotions using the promotion_id in the sales_fact_1997 table, which we can see within the Schema view of the FoodMart Sales cube. It appears, with the promotion_id column circled in red, as shown in Illustration 1.

Click for larger image

Illustration 1: The Promotion_Id Column in the Fact Table

A quick browse of the promotion table (which is joined to the sales_fact_1997 table via the promotion_id column), the results of which are partially depicted in Illustration 2, reveals that a promotion_id of 0, and a corresponding promotion name of "No Promotion," indicates the absence of a promotion.


Illustration 2: Partial Browse of the Promotion Table - Promotion_Id of 0

The MSAS administrator reasoned that this field could serve as the basis of a conditional expression, to the effect of "if the promotion_id equals zero, we need not apply any percentage increase to the existing store_cost value (and can accept its current value in our new measure)," as no additional promotion was performed for any associated items. If, however, the promotion_id equals anything except zero, she could apply the mandated fifteen percent upward adjustment to store_cost. The operation could then be handled using a relatively straightforward "IF-THEN" construct, as we shall see in the following steps, where we recreate the original calculated measure.

1.  Open Analysis Manager.

2.  Expand the Analysis Servers folder in the management console.

3.  Expand the Analysis Server with which you are working by clicking the "+" sign to its left.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder inside the FoodMart 2000 database.

6.  Right-click the Sales cube.

7.  Select Edit ... from the context menu that appears, as depicted in Illustration 3.


Illustration 3: Select Edit from the Context Menu

The Cube Editor opens.

8.  Right-click the Calculated Members folder within Cube Editor.

9.  Select New Calculated Member ... from the context menu that appears, as depicted in Illustration 4.


Illustration 4: Select New Calculated Member from the Context Menu

The Calculated Member Builder opens.

10.  Ensure that the Parent dimension is set at Measures, the default.

11.  Type the following into the Member name box:

CM_Cost with Promo Alloc

12.  Type the following MDX into the Value Expression section of the Calculated Member Builder:


IIF([Measures].[Unit Sales]=0,0,

       (IIF([Promotions].CurrentMember.Name ="No Promotion", 

         ([Measures].[Store Cost]),([Measures].[Store Cost]*1.15))))

The MDX expression above returns, via the calculated measure, the correctly adjusted Sales Cost value. An explanation of the components of the above expression appears in Table 1.

Expression

Meaning


IIF([Promotions].CurrentMember.Name ="No Promotion",

      [Measures].[Store Cost],

          [Measures].[Store Cost]*1.15))

Conditional logic is applied via the core IIF function. The IIF function in MDX can be used to perform simple, yes-or-no decisions. Here, if "No Promotion" (a zero promotion_id value) appears as the Promotions.CurrentMember value, the unadjusted Store_Cost measure is returned.

If the Promotions.CurrentMember is any value besides "No Promotion" (a non-zero promotion_id value), the Store_Cost value is multiplied by 1.15, returning the Store_Cost value, adjusted upward by fifteen percent, to add the mandated fixed promotion allocation to the total.

   IIF([Measures].[Unit Sales]=0,0, .... )

The "external" IIF function is merely in place to handle the situations that arise when no sales data exists for a given item, and to prevent error indications from appearing. The calculated measure thus returns a zero if no unit sales occurred, or the results of the inner, primary function if sales data does, indeed, exist.


Table 1: Expression Components Summary

NOTE: For an explanation of the .CurrentMember function, see my article MDX Essentials: MDX Member Functions: "Relative" Member Functions in the MDX Essentials series at Database Journal.

The Calculated Member Builder appears with our input as depicted in Illustration 5.


Illustration 5: Calculated Member Builder with Complete MDX Expression (Compressed View)

13.  Click the Check button to validate syntax.

The confirmation dialog appears, indicating valid syntax within the new calculated measure, as shown in Illustration 6.


Illustration 6: Confirmation Dialog Indicates Positive Syntax Validation

14.  Click OK to dismiss the confirmation dialog.

15.  Click OK to close the Calculated Member Builder.

The new calculated measure appears in the tree within the Calculated Members folder.

16.  With the CM_Cost with Promo Alloc calculated measure selected, click the Advanced tab in the Properties pane beneath.

17.  Ensure that the Format String is set as follows:

#,#.00

The CM_Cost with Promo Alloc calculated measure appears within the Calculated Members folder, with our selection of Advanced Properties - Format String setting, as depicted in Illustration 7.


Illustration 7: The New CM_Cost with Promo Alloc Calculated Measure

The other calculated members that appear in the illustration above may differ from your own, depending upon the activities that have been conducted with the Sales cube in your own environment. As another matter, although the actions we have performed to this point alone do not require it, let's process the cube to make sure we are all in a "processed" state.

18.  Select Tools --> Process Cube to process the Sales cube.

19.  Click Yes on the Save the Cube dialog that appears next, as shown in Illustration 8.


Illustration 8: Click "Yes" to Save the Cube

20.  Click No on the dialog that appears next, as shown in Illustration 9; we will not design aggregations at present.


Illustration 9: Click "No" to Designing Aggregations

The Process a Cube dialog appears, as depicted in Illustration 10, with the processing method set to Full Process. Full processing for the Sales cube will be relatively quick, so we will perform it to ensure that all is refreshed.


Illustration 10: Full Process Selected in the Process a Cube Dialog

21.  Ensure that the Full Process radio button is selected on the Process a Cube dialog.

22.  Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 11.


Illustration 11: Indication of Successful Processing Appears (Compact View)

23.  Click Close to dismiss the viewer.

24.  Click the Data tab in the Cube Editor, if necessary.

Cube data is retrieved, and we are able to see the values that appear for all measures, including the new calculated measure, in the Data view.

25.  Drag the Store Type dimension in the upper half of the Data view to the top of the left-most column in the lower half - in my case, I am dragging the Store Type dimension bar over the Country column heading, but your row axis may be different, depending upon previous activities with the cube.

Store Type appears in the place of the previously existing dimension in the row axis.

26.  Drag the Promotions dimension to the immediate right of the newly placed Store Type column, to effect a "crossjoin." When the cursor appears as shown in Illustration 12, drop the Promotions dimension.


Illustration 12: Cursor Indicates Appropriate Drop Point

The final arrangement should appear as depicted in Illustration 13.


Illustration 13: Arrangement in the Data View - Partial Row Axis

We can now see the calculated member exactly as it was constructed by the newly appointed MSAS Administrator. We will leave the calculated measure in place until after constructing and testing its replacement, for comparison purposes.

Procedure - Creating a Derived Measure

We will next create a derived measure, whose role will be to improve performance from the perspective of querying, and thus from that of the information consumers. The derived measure will be directly based upon columns in the FoodMart database, upon which we will use SQL to perform the required logic, as we shall see. The key reason, we will recall, for creating the derived measure is that the measure is calculated as a part of cube processing, and is stored completely in MSAS for rapid retrieval, versus being generated at runtime like the calculated measure.

We will name our derived measure DM_Cost with Promo Alloc, and, once we verify that it stores the desired values, will replace the MSAS Admin's calculated member that we recreated in the last section.

1.  In the tree pane of the Cube Editor, right click the Measures folder.

2.  Select New Measure from the context menu that appears, as shown in Illustration 14.

Click for larger image

Illustration 14: Select New Measure ...

The Insert Measure dialog box appears, offering us the appropriate columns in the designated fact table for the cube, sales_fact_1997.

3.  Click the store_cost column to select it, as depicted in Illustration 15.


Illustration 15: Select Store_Cost (Circled) ...

4.  Click OK to accept the selection, and to close the Insert Measure dialog.

The new measure appears in the tree, named Store Cost 1 by default (because a measure named Store Cost already exists in the cube), as shown in Illustration 16.


Illustration 16: The New Measure Appears ...

5.  Click the new Store Cost 1 measure to select it, if necessary.

We will now rename the measure, and then add the syntax to the Source Column property to enable the same logic in the measure that we saw in the original calculated measure, and which we have confirmed with management to be correct.

6.  Expand the Properties pane that appears below the tree if required, by clicking Properties, as depicted in Illustration 17.


Illustration 17: Upward Arrow Indicates Properties Pane is Collapsed ...

The Properties pane, Basic tab, appears, expanded, as shown in Illustration 18.


Illustration 18: Expanded Properties - Basic Tab

7.  In the Name property, replace the existing name, Store Cost 1, with the following name:

DM_Cost with Promo Alloc 

8.  Type the following directly into the Source Column property, replacing "sales_fact_1997"."store_cost":


IIf("sales_fact_1997"."promotion_id" = 0, 
  "sales_fact_1997"."store_cost", 
  "sales_fact_1997"."store_cost"* 1.15 )

The Properties pane, Basic tab, with our modifications, appears as depicted in Illustration 19.


Illustration 19: Properties Pane - Basics Tab, with our Modifications

The syntax we have typed into the Source Column property mirrors the logic behind the syntax for the calculated member, which we explained in Table 1 above. The syntax in the Source Column property must be appropriate to the underlying database upon which it is intended to operate (in this case, the MS Access Foodmart database). As might be expected, the syntax to obtain a given result will differ between RDBMS'.

9.  Click the Advanced tab of the Properties pane.

10.  Select the following Display Format:

#,#.00

The Properties pane, Advanced tab appears as depicted in Illustration 20.


Illustration 20: Properties Pane - Advanced Tab, with our Display Format Selection

11.  Select Tools --> Process Cube once again, to reprocess the cube.

12.  Click Yes when prompted to save the cube, as we did earlier.

13.  Click No upon the prompted offer to design storage, and complete the rest of the steps in reprocessing the cube as we did earlier.

Processing begins, as it did earlier, and concludes with the green Processing completed successfully message, as before.

14.  Click Close to dismiss the viewer.

Cube data is again retrieved (click the Data tab, as required), and we are once more able to see the values for all measures, including the new derived measure, DM_Cost with Promo Alloc. We will drag the MSAS Admin's calculated measure, CM_Cost with Promo Alloc, to the top of the members listed in the Calculated Members folder, and thus be able to see it side by side in the Data view with the new derived measure, DM_Cost with Promo Alloc, for comparison purposes, before we eliminate the calculated measure.

15.  Within the Calculated Members folder of the tree pane, click the calculated measure CM_Cost with Promo Alloc.

16.  Drag the calculated measure to the top of the tree within the Calculated Members folder.

The calculated measure CM_Cost with Promo Alloc appears atop the tree. There it is "next in line" to the measure at the bottom of the tree in the Measures folder, our new derived measure, DM_Cost with Promo Alloc, as shown in Illustration 21.


Illustration 21: Aligning the Derived and Calculated Measures for Comparison in the Data View

17.  In the Data view, reposition the row axes, if necessary, so that the row headings display the Store Type / Promotion Name combination we set up earlier.

18.  Scroll over in the Data view until the columns headed CM_Cost with Promo Alloc and DM_Cost with Promo Alloc appear, as depicted in Illustration 22.


Illustration 22: Aligning the Derived and Calculated Measures for Comparison in the Data View

Side by side, we can see that the derived measure appears to return the same value as the calculated measure, with two exceptions - both of which add to the desirability of the derived measure as a solution. First, there is a difference in the "All Promotions" value, atop the Promotion Name rows. This is due to the correct exclusion, by the derived measure, of the "No Promotion" promotion row from the fifteen percent uplift calculation. (Test the math, if desired).

The other difference is the placement of the zeros in the new derived measure column, which is a result of our "outer IIF" statement. The statement prevents an error that would be indicated were it not in place. (We could replace or hide the zero easily enough, but for this session, let's leave it in place.)

19.  Drill further or otherwise experiment with the derived measure to get a comfort level with its accuracy, if desired.

We will now eliminate the calculated measure, CM_Cost with Promo Alloc, as the derived measure has been verified to meet the business requirements (enhanced query performance and accurate results) specified by the information consumers.

20.  Right-click the CM_Cost with Promo Alloc calculated measure, within the tree pane of the Calculated Members folder.

21.  Select Delete from the context menu that appears, as shown in Illustration 23.


Illustration 23: Deleting the Calculated Measure ...

The calculated measure disappears from the tree pane.

22.  Select File --> Exit to leave the Cube Editor, when ready (saving the cube, if prompted).

We are returned to the Analysis Manager console.

23.  Select File --> Exit to leave Analysis Services, when desired.

Conclusion

In this article, we explored the use of derived measures to enhance cube query response time. Discussing the drawbacks in using calculated members in cases where a derived measure might be substituted, we considered the benefits and disadvantages that might accrue using derived measures. We then began a practice exercise, the preparation for which involved the creation of a simple calculated measure, which we used to serve as the "existing," less-than-optimal solution that had already been provided to our hypothetical group of information consumers. The calculated measure served as a basis for comparison with our more optimal solution, the derived measure.

We described the requirement of the information consumers to enhance query response time, and, in answer to their need, we determined that a derived measure might best be substituted for the existing calculated measure. We then implemented our solution through creation of a derived measure to replace the existing calculated measure. Finally we discussed the results obtained, verifying the values provided by our solution against those produced by the calculated measure, before eliminating the latter from the cube.

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

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved