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