Perspectives within MDX
In Mastering Enterprise BI: Introduction to
we discussed a simple illustration of a possible use for a Perspective within
the Adventure Works cube, which makes its home in the Adventure Works
DW sample Analysis Services 2005 database. The
sample ships with twenty-one cube dimensions and eleven Measure
Groups, representing sales, sales forecasting and financial data.
While a client application might directly access the whole cube, such an
all-encompassing viewpoint might overwhelm a consumer whose business needs are
limited, say, to basic sales forecasting information. Instead of
subjecting the consumer to such an overload scenario, not to mention exposing
data, perhaps, for which the consumer has no "need to know," we can
implement a Sales Targets Perspective to narrow this consumer's view to
the objects relevant to doing his job, providing support for forecasting sales.
important to remember, as we create and assign Perspectives within our
local environments, that their purposes are to afford easier navigation,
querying and other interaction with the cube. Perspectives do not
physically restrict access to cube objects, nor do they prevent direct
referencing or retrieval of the objects through MDX, XML,
or DMX statements, as we shall see, in many cases. Because these
viewpoints comprise read-only views of the cubes with which they are
associated, users cannot change (rename, etc.), or modify the behavior or
features of, cube objects through the use of Perspectives.
We will examine the simple MDX syntax for using a Perspective
after our customary overview in the Discussion section that
follows. Following that, we will conduct practice examples within a
couple of scenarios, constructed to support hypothetical business needs that
illustrate uses for (and limitations of) Perspectives in general.
This will afford us an opportunity to explore some of the delivery options that
Perspectives can offer the knowledgeable user. Hands-on practice
with Perspectives, where we will create queries that employ them as
"sources", will help us to activate what we
have learned in the Discussion and Syntax sections.
restate our initial explanation of their primary intended function, Perspectives
allow us to designate portions or sections of the UDM for specific
focus, which we can then use for access to the defined slice / section of the UDM.
As we noted earlier, we can include Dimensions, Attributes, Hierarchies,
Measure Groups, Measures, and other objects within a Perspective it
really becomes a process of what, from the default set of "all
objects" that we wish to eliminate from the Perspective. Perspectives can thus be likened to a
"virtual subcube," or perhaps to a "view" of a UDM.
further illustrate the nature of a Perspective,
let's take a quick look at several that have been created within the Adventure
Works DW sample Analysis Services Database that ships with MSSQL
Works DW database and companion samples are not installed by default in MSSQL
Server 2005. The samples can be installed during Setup, or at any
time after MSSQL Server has been installed.
The topics "Running Setup to
Install AdventureWorks Sample Databases and Samples"
in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in
the Books Online (both of which are included on the installation
CD(s), and are available from www.Microsoft.com
and other sources), provide guidance on samples installation. Important
information regarding the rights / privileges required to accomplish samples
installation, as well as to access the samples once installed, is included in
the Sample Analysis Services Database in Business Intelligence Development
access the sample Adventure Works DW database within the Business
Intelligence Development Studio, where we typically accomplish design and
development work in general. Our intent is simply to examine sample Perspectives
before querying them, to have a grasp of the structures with which we are
Click the Start button
Select Microsoft SQL
Server 2005 within the Program group of the menu.
Click SQL Server
Business Intelligence Development Studio, as depicted in Illustration 1.
Opening SQL Server Business Intelligence Development Studio
The Microsoft Visual
Studio 2005 development environment opens, beginning with the Start page,
as shown in Illustration 2.
Illustration 2: The
Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed
Close the Start
Select File -à Open on the Visual Studio main
Services Database from the cascading menu, as depicted in Illustration 3.
3: Opening an Analysis Services Database ...
The Connect to
Database dialog appears.
Ensure that the radio
button to the immediate left of Connect to existing database is
Type the name of the
server / server with instance into the Server box. (Mine is MOTHER1\MSSQL2K5
as seen in various illustrations throughout my articles)
Select the Adventure
Works DW database in the Database selector underneath the Server
The relevant portion of
the Connect to Database dialog appears, with our input, similar to that
shown in Illustration 4.
Illustration 4: The
Connect to Database Dialog with our Input / Selections
Click OK to
accept our input, and to connect to the Analysis Services database.
The Connect to
Database dialog closes, as a brief message box appears, indicating
"Reading data from the server." We see the sample Analysis
Services database open, complete with all member objects, in Solution
Explorer, which appears, by default, in the upper right corner of
the BI Development Studio. All that remains is to open the Cube
Designer for the Adventure Works cube, to allow us access to the Perspectives
tab, where its Perspectives were designed, and are maintained.
Right-click the Adventure
Works cube in the Solution Explorer.
as depicted in Illustration 5.
Opening the Cube Designer in the SQL Server BI Development Studio
The Cube Designer opens,
and appears as shown in Illustration 6.
Cube Designer - Compressed View
Let's examine the Perspectives to get some experience with
the process. The Adventure Works DW sample database contains an
extensive set of sample objects upon which we can rely for training and testing
purposes. Of immediate interest are the several examples of Perspectives
that have been created, and can be reviewed within the Cube Designer,
to give us more of an idea of what is going on "under the hood," when
we target a Perspective with an MDX query.
noted earlier, we can select our choice of cube objects (including dimensions,
attributes, hierarchies, measure groups, measures, calculations, KPIs and actions) for inclusion within each Perspective.
Let's examine how this was done within the working
examples found in our sample database by taking the following steps:
13. Click the Perspectives tab
atop the Cube Designer, as depicted in Illustration 7.
7: Accessing Perspectives ...
The Perspectives page appears. Here, we see a list
of the Cube Objects making up each Perspective, grouped by object
type, and subgrouped by parent structures, as
appropriate. Here, we can see the details for the following sample Perspectives:
The Perspectives appear together on the Perspectives
page, as partially shown in Illustration 8.
Illustration 8: The
Perspectives Page within the Sample Environment
With each of the Perspectives, we can see the
objects that have been included, and that thus appear when the Perspective
is selected in the Cube Browser, as well as client applications (such as
Reporting Services and other query / analysis tools). As I have stated here and elsewhere, Perspective creation is
largely a process of excluding (via de-selection of objects which are
all selected by default in a new Perspective) unwanted objects.
NOTE: For the detailed steps
involved in setting up, maintaining and browsing Perspectives from
within the Cube Designer, see my article Mastering
Enterprise BI: Introduction to Perspectives.
Examine the Perspectives
further, as desired.
Leave the Business
Intelligence Development Studio open for easy reference to the Perspective
settings as we proceed with the Practice section that follows.
we have a clear idea of what we intend to target via MDX queries within our
practice session, let's look at syntax specifics to
further clarify the use of Perspectives within our MDX queries.