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 ...
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.
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:
drawbacks in using calculated members in cases where a derived measure might be
benefits and disadvantages that might accrue through the use of derived
illustrative scenario, upon which we will determine that a derived measure can
offer a tuning solution for a group of hypothetical information consumers;
simple solution through creation of a derived measure to replace an existing calculated
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.
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.
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
- 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.
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
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
Expand the Analysis
Servers folder in the management console.
Expand the Analysis
Server with which you are working by clicking the "+" sign to its
Expand the FoodMart
Expand the Cubes
folder inside the FoodMart 2000 database.
the Sales cube.
... from the context menu that appears, as depicted in Illustration 3.
Illustration 3: Select
Edit from the Context Menu
the Calculated Members folder within Cube Editor.
Calculated Member ... from the context menu that appears, as depicted in Illustration
Illustration 4: Select
New Calculated Member from the Context Menu
Member Builder opens.
the Parent dimension is set at Measures, the default.
following into the Member name box:
CM_Cost with Promo Alloc
following MDX into the Value Expression section of the Calculated
(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.
Table 1: Expression
IIF([Promotions].CurrentMember.Name ="No Promotion",
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.
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.
NOTE: For an explanation of the .CurrentMember function, see my
Essentials: MDX Member Functions: "Relative" Member Functions in
the MDX Essentials series at Database
Member Builder appears with our input as depicted in Illustration 5.
Calculated Member Builder with Complete MDX Expression (Compressed View)
Click the Check
button to validate syntax.
confirmation dialog appears, indicating valid syntax within the new calculated
measure, as shown in Illustration 6.
Illustration 6: Confirmation
Dialog Indicates Positive Syntax Validation
Click OK to
dismiss the confirmation dialog.
Click OK to
close the Calculated Member Builder.
new calculated measure appears in the tree within the Calculated
With the CM_Cost
with Promo Alloc calculated measure selected, click the Advanced tab
in the Properties pane beneath.
the Format String is set as follows:
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
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.
--> Process Cube to process the Sales cube.
on the Save the Cube dialog that appears next, as shown in Illustration
Illustration 8: Click "Yes"
to Save the Cube
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
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
the Full Process radio button is selected on the Process a Cube
to begin 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)
to dismiss the viewer.
Click the Data
tab in the Cube Editor, if necessary.
is retrieved, and we are able to see the values that appear for all measures,
including the new calculated measure, in the Data view.
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.
Type appears in
the place of the previously existing dimension in the row axis.
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
arrangement should appear as depicted in Illustration 13.
Illustration 13: Arrangement
in the Data View - Partial Row Axis
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.
- Creating a Derived Measure
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
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.
In the tree
pane of the Cube Editor, right click the Measures folder.
Measure from the context menu that appears, as shown in Illustration 14.
Measure dialog box appears, offering us the appropriate columns in the
designated fact table for the cube, sales_fact_1997.
Click the store_cost
column to select it, as depicted in Illustration 15.
Illustration 15: Select
Store_Cost (Circled) ...
to accept the selection, and to close the Insert Measure dialog.
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
Illustration 16: The New
Measure Appears ...
Click the new Store
Cost 1 measure to select it, if necessary.
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.
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 ...
pane, Basic tab, appears, expanded, as shown in Illustration 18.
Illustration 18: Expanded
Properties - Basic Tab
In the Name
property, replace the existing name, Store Cost 1, with the following
DM_Cost with Promo Alloc
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"* 1.15 )
pane, Basic tab, with our modifications, appears as depicted in Illustration
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:
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.
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.