Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances

Monday Dec 13th 2004 by William Pearson
Share:

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 ...

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 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

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, across all.

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.

In 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 the semi-additive measures, we will accomplish the following:

  • Create a copy of the Warehouse sample cube for use in our practice exercise;
  • Prepare the cube further by processing;
  • Perform a 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;
  • Explore an 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;
  • Discuss the 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 month.

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.

Hands-On Procedure

Preparation

Create 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.

1.  Open Analysis Manager, beginning at the Start menu.

2.  Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.

Our server(s) appear.

3.  Expand the desired server.

Our database(s) appear, in much the same manner as shown in Illustration 1.


Illustration 1: A Sample Set of Databases Displayed within Analysis Manager

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder.

The 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 Online.

6.  Right-click on the Warehouse sample cube.

Again, we 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.

7.  Select Copy from the context menu that appears.

8.  Right-click on the Cubes folder.

9.  Select Paste from the context menu that appears.

The Duplicate Name dialog appears.

As noted in previous articles, we cannot have two cubes of the same name in a given MSAS database.

10.  Type the following into the Name box of the Duplicate Name dialog:

DBJ_SemiAdd

The Duplicate 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.

11.  Click OK to apply the name change.

The new 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.

Process the Clone Cube

1.  Right-click the new DBJ_SemiAdd cube.

2.  Select Process... from the context menu that appears, as depicted in Illustration 4.


Illustration 4: Select Process... from the Context Menu

The Process 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 processed).


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

3.  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 6.


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

4.  Click Close to dismiss the viewer.

We are 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.

Procedure

Add 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.

1.  Right-click the DBJ_SemiAdd cube, in the cube tree in Analysis Manager.

2.  Select Edit to open the Cube Editor.

3.  Within the Cube Editor, click the Data tab.

Cube data is retrieved, and we are able to see the values that appear for all measures, including the two inventory-related measures we have identified.

4.  Click the Time dimension button in the Filter area of the Data tab.

5.  Drag the Time button onto the top of the Product Family row heading in the Data Grid area.

6.  Drop the Time button onto the Product Family row heading, to swap the Time dimension for the Product dimension in the Row axis.

The "swap" procedure we are undertaking is shown in Illustration 8.


Illustration 8: Swapping the Time Dimension into the Row Axis (Data Grid - Partial View)

The Time dimension now appears on the Row Axis.

7.  Select Insert from the main menu of the Cube Editor.

8.  Select Calculated Member from the cascading menu that appears, as depicted in Illustration 9.


Illustration 9: Select Insert --> Calculated Member

The Calculated Member Builder appears.

9.  Type the following into the Member name box:

Stock Level

10.  Type the following into the Value expression box:

[Measures].[Units Ordered] - [Measures].[Units Shipped]

11.  Click the Check button, at the upper right of the Value expression box, to perform a simple syntax check.

Analysis Manager generates a message box, indicating that "Syntax is OK," as shown in Illustration 10.


Illustration 10: Simple Syntax Check Is Positive ...

12.  Click OK, to close the message box.

The Calculated Member Builder appears as depicted in Illustration 11.


Illustration 11: Calculated Member Builder with Our Additions (Compact View)

13.  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 Stock 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)

Now, let's refine the Data Grid to reflect our immediate concentration.

14.  Click the Warehouse dimension button in the Filter area of the Data tab.

15.  Drag the Warehouse button onto the top of the MeasuresLevel row heading in the Data Grid area.

16.  Drop the Warehouse button onto the MeasuresLevel row heading, to swap the Warehouse dimension with MeauresLevel in the Data Grid.

The "swap" procedure we are undertaking is depicted in Illustration 13.

[DJ_ANSYS30-013]


Illustration 13: Swapping the Warehouse Dimension With MeasuresLevel

(Data Grid - Partial View)

The Warehouse 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 View)

Let's 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.

17.  Right-click the All Warehouses heading that appears immediately underneath the Country heading we noted above.

18.  Select Drill Up from the context menu that appears, as depicted in Illustration 15.


Illustration 15: Select Drill Up to Contract the Warehouse Dimension - Top Level

The Warehouse 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 measure.

19.  Select Stock 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]) - 

    (ClosingPeriod([Month]),[Measures].[Units Shipped])

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.

Conclusion

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.

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