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 2

By William Pearson

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


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:


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.

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