Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 13, 2004

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

By William Pearson


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.


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM