Create a Cube-Based Hierarchical Picklist
Objective and Business Scenario
In the following
sections, we will perform the steps required to create calculated members and
a named set within a sample cube, to illustrate cube-level support for
an organizational reporting function. Once we have accomplished the simple
structural additions to MSAS, we will construct a parameter within a basic report
that we have created in Reporting Services. We will base the parameterization
of the report upon the calculated members and a named set that we
have created in MSAS.
For purposes of our
practice procedure, we will assume that information consumers within the
Finance department of the FoodMart organization have expressed the need for a
single, prompted parameter, based upon geographical location of their stores,
within a basic report that focuses on total expense for a given location. While
the consumers wish the capability to narrow the report to a view of the
expenses of individual stores at any time, they have also expressed that
a "nice to have" would be the capability to select, within the same
report, upon various other hierarchical levels in location, such as city,
state and so forth.
recognize that such a hierarchical picklist might be valuable in many other
reports, and we decide to make the investment in creating the support for this
functionality in the MSAS cube. We can then reuse the underlying structure easily
in prospective efforts, by simply referencing it in any report we author.
We realize that other
parts of the organization, who report from MSAS cubes through various
applications, will be able to leverage the structure we provide in this manner:
Any application that can use a calculated member / named set can
take advantage of these components with ease. Moreover, we can see that the requested
selection capability is applicable within other dimensions, as well, and that
the concepts involved can be put in place elsewhere.
Obvious benefits are
many, and include easier, more consistent reporting (to a degree, perhaps, "managed
authoring...") as well as greater functionality centralized within a given
report. The reports can be used to retrieve a wider range of information within
a single report, meaning a dramatic reduction in the number of individual
reports in the library that, in essence, accomplish the same objectives at
different "rollup," and other, levels.
Considerations and Comments
For purposes of this
exercise, we will prepare a copy of the Budget cube in the FoodMart
2000 sample database, which accompanies the installation of MSAS, along
with other samples. The "clone" will allow us to leave the original
sample cube in its pristine (or otherwise existing) condition, as we might have
saved various settings, structures, and so forth, for referential or other
reasons. There will therefore be no need to remember to return and remove
settings that we modify for purposes of the lesson, or otherwise restore the
original sample to its previous state. We can simply discard our clone upon
the conclusion of our session, or at any convenient time thereafter.
While the cloning
process is simple, ensure that you have the authority, access and privileges
needed to accomplish the process, and that the copy of an existing MSAS cube
within the FoodMart 2000 database presents no other issues in your
environment. After the session, the clone can be deleted or used for another
purpose, whatever is convenient.
If the sample database
was not installed, or was removed prior to your beginning this article, please
see the MSAS documentation, including the Books Online, for the
straightforward procedure to restore the database from the archive (.cab)
file containing the samples. As of this writing, a copy of the archive can be
obtained from the installation CD or via download from the appropriate
copy the existing cube to provide a disposable work environment.
Expand the Analysis
Server folder in the management console.
Expand the Analysis
Server with which you are working by clicking the "+" sign to its
Expand the FoodMart
Expand the Cubes
folder inside the FoodMart 2000 database.
the Budget cube.
from the context menu that appears, as shown in Illustration 1.
Illustration 1: Select
Copy from the Context Menu
the Cubes folder.
from the context menu that appears, as shown in Illustration 2.
Illustration 2: Select
Paste, after Right-clicking the Cubes Folder
Name dialog appears.
Rename the new
database as follows:
This is also a good way
to rename MSAS objects for which a "Rename" option
does not exist. We simply create the new object in the manner shown above,
give it the desired name, and discard the original object, as appropriate.
Keep in mind that, in
the case of cubes and other structural objects, this will mean reprocessing
before the clone will be fully usable.
Name dialog appears as depicted in Illustration 3.
Duplicate Name Dialog with Our Input
Click OK to
create the clone cube.
Exp_Finance cube appears in the tree as shown in Illustration 4.
Illustration 4: The New