MSAS Architect Bill Pearson returns
with an introduction to semi-additive measures, in a hands-on approach to
meeting a common business requirement.
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 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.
Most of the measures
with which we work in our daily Analysis Services environments are additive,
and include various options for easy aggregation, comprised of the
ever-familiar SUM, MAX, MIN and COUNT. Most base
measures involving transactions, such as sales or direct expenses, are
inherently additive. We typically find additive measures simple and useful in
our work within analysis and reporting systems, because there are no inherent
restrictions on how they are used in our cubes. Such measures can be sliced and
diced in any "direction," for example. Using the four aggregation
types to derive aggregates from previously aggregated results is only one
example of how we can easily leverage the power of OLAP as implemented in
MSAS. With additive measures, aggregation is applied consistently to all
dimensions: the measures roll up equally well, within the same aggregation type,
However, as most of us
are aware, semi-additive measures exist in the business environment, as
well. Periodic measurements, such as account balances (for example, the
daily balance of a bank account), level measurements (such as on-hand inventory
quantities or personnel headcounts), and the like, do not share the qualities
of fully additive measures. Semi-additive measures are additive across some
dimensions within the cubes they inhabit, but are not additive across
one or more of the dimensions of the cube.
As an illustration, an inventory
level might be additive along the Product, Store and Warehouse
dimensions of a cube, but would be non-additive across the Time
dimension of the cube. Alternatively, a daily bank account balance might
certainly be aggregated usefully in an average over Time
(a common case would be an average daily balance), and perhaps in minimum
and maximum contexts, but summing the daily balance over time would
present a meaningless result.
this article, we will explore the management of semi-additive measures,
creating a calculated measure (a calculated member that belongs to the Measures
dimension) that is not fully additive, to meet the business requirements
of a hypothetical group of information consumers. Within our exploration of
measures, we will accomplish the following:
Create a copy
of the Warehouse sample cube for use in our practice exercise;
cube further by processing;
practice exercise, using an illustrative set of business requirements as a
specification for creating a semi-additive measure (a calculated measure)
in our practice cube;
initial approach to creating the simple inventory balance calculated measure,
and explain its shortcomings as a fully additive measure;
Modify the calculated
measure to cause it to exhibit the appropriate semi-additive behavior;
results datasets obtained within the steps of our practice example.
Generating Periodic Balances
Overview and Discussion
In this article, we
will examine the management of semi-additive measures. As a part of our
introduction to semi-additive measures, we will create a calculated measure
that exhibits the attributes of a semi-additive measure. Our practice exercise
will provide a basis for our discussion of managing such measures, as well as
demonstrating an approach for creating a simple measure to support the inventory
level analysis and reporting requirements of information consumers.
For purposes of our
practice procedure, we will assume that we have been contacted by a group of
information consumers in the Accounting department of the FoodMart
organization, who wish to perform some high level analysis and reporting on
inventory levels based upon data stored in the Warehouse sample cube.
While a true inventory account, per se, does not exist within this simple cube,
we inform the consumers that we can at least partly accommodate their need with
a calculated member that will generate a rough inventory balance from month to
The consumers, who are
themselves the intended audience, seek to use the basic inventory balance
measure to explore its general operation, with an ultimate objective demonstrating
the value of developing more involved inventory analysis capabilities as an
independent project in the next fiscal year.
We listen closely to
the requirement, and then develop a plan to deliver the basic capabilities with
a calculated member. We decide to work with a copy of the Warehouse cube to
allow the original to remain in use at the same time, as well as to protect the
original from any unintended loss of existing capabilities.
Considerations and Comments
For purposes of this
exercise, we will create a copy of the targeted cube, as we have in various
articles of this and other series. We will then process the clone cube to "register"
it with Analysis Services, before beginning our addition of calculated members.
a Clone Cube
Let's get started by
creating a clone of the Warehouse FoodMart sample cube. This will
allow us to keep the original cube intact for other uses.
Manager, beginning at the Start menu.
Expand the Analysis
Servers folder by clicking the "+" sign to its immediate
in much the same manner as shown in Illustration 1.
Illustration 1: A Sample
Set of Databases Displayed within Analysis Manager
Expand the FoodMart
Expand the Cubes
sample cubes appear,
as shown in Illustration 2.
Illustration 2: The
Sample Cubes in the FoodMart 2000 Database
NOTE: Your local databases / cube tree will differ, depending
upon the activities you have performed since the installation of MSAS (and the
simultaneous creation of the original set of sample cubes). Should you want or
need to restore the cubes to their original state, simply restore the database
under consideration. For instructions, see the MSSQL Server 2000 Books
the Warehouse sample cube.
are making a copy of the Warehouse cube, because our lesson will involve
making changes to the cube we use within the practice example. As we have
noted, working with the copy will allow us to maintain our existing sample cube
in its current condition, and available to other users.
from the context menu that appears.
the Cubes folder.
from the context menu that appears.
Name dialog appears.
in previous articles, we cannot have two cubes of the same name in a given MSAS
following into the Name box of the Duplicate Name dialog:
Name dialog appears, with our modification, as depicted in Illustration 3.
Illustration 3: The
Duplicate Name Dialog, with New Name
TIP: As I have mentioned elsewhere in
this and other series, the foregoing is also an excellent way of renaming
a cube (a "rename" capability is not available here, as it is in
many Windows applications). Simply create a duplicate, give it the name to
which you wish to rename the old cube, and then delete the old cube, as
appropriate (although not in this case, of course). This also works for MSAS databases,
dimensions and other objects.
to apply the name change.
cube appears in the cube tree, among those already in place. We now have a
copy of the Warehouse cube, DBJ_SemiAdd, within which we can perform
the steps of our practice exercise. Let's process the new cube to "register"
it with Analysis Services, and to make sure we are all in a "processed" state.
the Clone Cube
the new DBJ_SemiAdd
2. Select Process... from the
context menu that appears, as depicted in Illustration 4.
Illustration 4: Select
Process... from the Context Menu
a Cube dialog appears, as depicted in Illustration 5, with the processing method
defaulted to Full Process (as this is the first time the cube has been
Illustration 5: Full
Process Selected in the Process a Cube Dialog
to begin processing.
begins. The Process viewer displays various logged events, then presents
a green Processing completed successfully message, as shown in Illustration 6.
Indication of Successful Processing Appears (Compact View)
to dismiss the viewer.
now ready to add the calculated member that will endow the DBJ_SemiAdd cube with the semi-additive
measure that forms the focus of our session.
Semi-Additive Calculated Members to Support Inventory Requirements
We now have a cube
within which to build the calculated member which will act as a semi-additive
inventory measure. Since no inventory account exists, and since the business
requirement is to create a "quick and dirty" inventory level measure
for purposes of demonstrating the behavior of a semi-additive measure within
MSAS, we will rely upon the Product measures in place to serve as a basis
for deriving our new measure.
The DBJ_SemiAdd cube contains two measures, Units
Shipped and Units Ordered, which we will use as the basis for our
product stocking levels. The measures are depicted in Illustration 7.
Illustration 7: Two
Inventory-Related Measures in the Clone Cube
Although it is,
admittedly, a rough means of coming to an inventory balance, we remind
ourselves that our focus is to produce a conceptual environment to demonstrate
the general behavior of semi-additive measures. We can assume for our purposes
that Units Ordered less Units Shipped for a given month (as an
example time period), equals units that remain, or, in effect, Product
stock on hand. Let's create a calculated member based upon this logic, and
delve into the considerations surrounding semi-additive measures as we proceed.
the DBJ_SemiAdd cube, in the cube tree in Analysis
to open the Cube Editor.
Within the Cube
Editor, click the Data tab.
is retrieved, and we are able to see the values that appear for all measures,
including the two inventory-related measures we have identified.
Click the Time
dimension button in the Filter area of the Data tab.
Drag the Time
button onto the top of the Product Family row heading in the Data
Drop the Time
button onto the Product Family row heading, to swap the Time
dimension for the Product dimension in the Row axis.
procedure we are undertaking is shown in Illustration 8.
Illustration 8: Swapping
the Time Dimension into the Row Axis (Data Grid - Partial View)
dimension now appears on the Row Axis.
from the main menu of the Cube Editor.
Member from the cascading menu that appears, as depicted in Illustration
Illustration 9: Select
Insert --> Calculated Member
Member Builder appears.
following into the Member name box:
following into the Value expression box:
[Measures].[Units Ordered] - [Measures].[Units Shipped]
Click the Check
button, at the upper right of the Value expression box, to perform a
simple syntax check.
Manager generates a message box, indicating that "Syntax is OK," as
shown in Illustration 10.
Illustration 10: Simple
Syntax Check Is Positive ...
to close the message box.
Member Builder appears as depicted in Illustration 11.
Illustration 11: Calculated
Member Builder with Our Additions (Compact View)
to accept our input and close the Calculated Member Builder.
Member Builder closes, and we are returned to the Data view of the Cube
Level calculated member appears in the Data Grid, as shown circled
in Illustration 12.
Illustration 12: Stock
Level Calculated Member Appears in Data Grid (Partial View)
refine the Data Grid to reflect our immediate concentration.
Click the Warehouse
dimension button in the Filter area of the Data tab.
Drag the Warehouse
button onto the top of the MeasuresLevel row heading in the Data
Drop the Warehouse
button onto the MeasuresLevel row heading, to swap the Warehouse
dimension with MeauresLevel in the Data Grid.
procedure we are undertaking is depicted in Illustration 13.
Illustration 13: Swapping
the Warehouse Dimension With MeasuresLevel
(Data Grid - Partial
dimension now appears (manifested as the Country column heading),
as shown (circled) in Illustration 14.
Illustration 14: Warehouse
Dimension in the Column Heading
(Data Grid - Partial
collapse the Warehouse dimension, which defaulted to expanded mode upon its
placement in the steps above, again to refine the view to our immediate focus,
the behavior of the Stock Level calculated member.
the All Warehouses heading that appears immediately underneath the Country
heading we noted above.
Up from the context menu that appears, as depicted in Illustration 15.
Illustration 15: Select
Drill Up to Contract the Warehouse Dimension - Top Level
dimension collapses to a single column. We now need to filter the values that
appear in the Data Grid. The values currently represent an aggregate of
all measures. We wish to filter the values to isolate our new Stock Level
Level from Measures, which we moved to the Filter section in
the swap we made with the Warehouse dimension in the immediately
preceding steps, as shown in Illustration 16.
Illustration 16: Filtering
by the New Stock Level (Calculated) Measure
Finally, we will expand the Time dimension to make the workings of our Stock Level measure readily apparent, with regard to how it accumulates value over time.
20. Right-click the Year heading over the Row Axis.
21. Select Drill Down from the context menu that appears, as depicted in Illustration 17.
Illustration 17: Select Drill Down to Expand the Year Level - Time Dimension
The Year level expands, revealing the underlying Quarter level.
22. Drill down on the Quarter level, just as we did for the Year level above.
The Data Grid now resembles that shown in Illustration 18.
Illustration 18: Data Grid - Drilled to Month Level
Let's take a look at a zoom of a single year to get a feel for what is happening in our Data Grid. As we can see from the two annotated examples below, the Year 1997 Total (the "sum of the 2's," in Illustration 19) is a simple total of the four Quarters that make it up, and the Q1 Total (the "sum of the 1's," in Illustration 19), represents a simple total of its member Months 1, 2, and 3. This behavior is consistent throughout the dataset, although we use Year 1997 only in our examples in Illustration 19.
Illustration 19: Fully Additive Behavior of Stock Level in Our DataSet (Year 1997 Only)
While this simple summing would be quite appropriate for many measures (such as sales, expense, and other common income statement accounts, among others), semi-additive measures such as Stock Level do not, as we have discussed, appropriately add up in this way. For this reason, we need to make an adjustment in our calculated member that takes into consideration the fact that we wish to present the balance at the end of each time period as that of the last member of the respective period (say Month 3 of Q1, or Q4 of Year 1997, in our two illustrated examples above),
Let's make our adjustment now, and ascertain the correct results with the modified Stock Level calculated member.
23. Right-click the Stock Level calculated member.
24. Select Edit from the context menu that appears.
25. Modify the Value expression to the following:
(ClosingPeriod([Month]),[Measures].[Units Ordered]) -
26. Click the Check button, to the upper right of the Value expression box, to perform a simple syntax check, once again.
Analysis Manager generates a message box, indicating that "Syntax is OK", once more. (If this is not the result, check your syntax input).
27. Click OK, to close the message box.
The Calculated Member Builder appears, with our modifications, as depicted in Illustration 20.
Illustration 20: Calculated Member Builder with Our Modifications (Compact View)
28. Click OK, to accept our input and close the Calculated Member Builder.
The Calculated Member Builder closes, and we are returned to the Data view of the Cube Editor. The Data Grid should reappear, with new values to reflect our changes, as shown in Illustration 21.
Illustration 21: New Values in the Data Grid ...
A quick review of the values reveals that we have, indeed, accomplished our objective, modifying the behavior of Stock Level measure to reflect its intended, semi-additive nature. As we see in the subset of the data, wherein we highlight parallel examples to those we noted above, the balance found in the "last" member (for both Quarter and Year) is the value retained as the "final" balance of the respective parent.
Illustration 22: Semi-Additive Behavior of Stock Level in Our DataSet (Year 1997 Only, Again)
NOTE: For more information on the ClosingPeriod() function, see my Database Journal article MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions.
29. Select File --> Exit to leave the Cube Editor, when ready (saving the cube, when prompted).
We are returned to the Analysis Manager console.
30. Select File --> Exit to leave Analysis Services, when desired.
In this article, we explored the nature and management of semi-additive measures, creating a calculated measure that is not fully additive, to meet the business requirements of a hypothetical group of information consumers. After discussing the nature of semi-additive measures, and describing uses for which they are appropriately employed, we created and processed a copy of the Warehouse sample cube in preparation for our practice example. We next established an illustrative set of business requirements as a specification for creating a semi-additive measure (a calculated member) in our practice cube.
We explored an initial approach to creating a simple inventory balance calculated measure, and explained why it was ineffective for our needs, due to its fully additive nature. We then modified the calculated measure to exhibit the desired semi-additive behavior of an inventory balance measure. Throughout the practice example, we discussed the results datasets obtained, within each of the steps we undertook to meet the expressed business requirements, verifying the effectiveness of our final solution.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.